A Cartesian Product occurs when Oracle joins all of the rows in one table to all of the rows in another table. If the number of rows in one of those tables is zero or one, this will not be a problem. However consider the consequences if both tables have more than 100,000 rows.100,000 x 100,000 = 100,000,000,000. ie. 100 billion rows. Clearly this will not run fast.
To find out whether your SQL is performing a Cartesian Product, run it through Explain Plan and search for a step that looks like this:
MERGE JOIN (CARTESIAN)There are several situations where Oracle will use a Cartesian Product
SELECT a.column1, b.column3
FROM table_a a, table_b b
WHERE a.column9 = 'X'
AND b.column5 >= to_date('13-JAN-2003','DD-MON-YYYY')
This is usually a programming error. Add join conditions and re-test. Better still, use ANSI join syntax in V9i+; it will force you to supply a join predicate (unless you specify the CROSS JOIN method).
Oracle thinks that one of the tables in the join has zero or one rows. If this is actually the case, the Cartiesian Product is fine. Otherwise it is likely that the statistics are missing on one or both tables: check the statistics and recalculate if necessary.
Oracle is performing a Star Query. However this usually only happens when you supply the STAR hint. Try it without the STAR hint and see if it imporves.
Oracle is performing a Star Join. However this usually only happens when you supply the ORDERED hint. Try it without the ORDERED hint and see if it imporves.
If you have appropriate join predicates, up to date statistics, no hints, and tables selecting 0 or 1 row, then Oracle should not be performing a Cartesian Product. If it is still happending, look at your join prediactes and consider the best order to join the tables. Place the tables in the FROM clause in that order and add the ORDERED hint to the SQL. This should stop the cartesian product. If it is still slow, continue reading this Guide for other alternatives.